#!/usr/bin/env python3
"""
MySQL连接调试脚本
逐步检查连接和查询问题
"""

import pymysql
from datetime import datetime

def test_mysql_connection_step_by_step():
    """逐步测试MySQL连接"""
    print("🔍 MySQL连接调试")
    print("=" * 50)
    
    # 步骤1: 测试基本连接
    print("步骤1: 测试基本连接...")
    try:
        conn = pymysql.connect(
            host="localhost",
            user="root",
            password="123456",
            database="kylin_2025",
            charset="utf8mb4",
            connect_timeout=10
        )
        print("✅ 基本连接成功")
        conn.close()
    except Exception as e:
        print(f"❌ 基本连接失败: {e}")
        return False
    
    # 步骤2: 测试数据库是否存在
    print("\n步骤2: 测试数据库是否存在...")
    try:
        conn = pymysql.connect(
            host="localhost",
            user="root",
            password="123456",
            charset="utf8mb4",
            connect_timeout=10
        )
        
        with conn.cursor() as cursor:
            cursor.execute("SHOW DATABASES LIKE 'kylin_2025'")
            databases = cursor.fetchall()
            if databases:
                print("✅ kylin_2025 数据库存在")
            else:
                print("❌ kylin_2025 数据库不存在")
                return False
        
        conn.close()
    except Exception as e:
        print(f"❌ 检查数据库失败: {e}")
        return False
    
    # 步骤3: 测试表是否存在
    print("\n步骤3: 测试表是否存在...")
    try:
        conn = pymysql.connect(
            host="localhost",
            user="root",
            password="123456",
            database="kylin_2025",
            charset="utf8mb4",
            connect_timeout=10
        )
        
        with conn.cursor() as cursor:
            cursor.execute("SHOW TABLES LIKE 'execution_plans'")
            tables = cursor.fetchall()
            if tables:
                print("✅ execution_plans 表存在")
            else:
                print("❌ execution_plans 表不存在")
                return False
        
        conn.close()
    except Exception as e:
        print(f"❌ 检查表失败: {e}")
        return False
    
    # 步骤4: 测试简单查询
    print("\n步骤4: 测试简单查询...")
    try:
        conn = pymysql.connect(
            host="localhost",
            user="root",
            password="123456",
            database="kylin_2025",
            charset="utf8mb4",
            connect_timeout=10
        )
        
        with conn.cursor() as cursor:
            cursor.execute("SELECT COUNT(*) FROM execution_plans")
            result = cursor.fetchone()
            count = result[0] if result else 0
            print(f"✅ 简单查询成功，记录数: {count}")
        
        conn.close()
    except Exception as e:
        print(f"❌ 简单查询失败: {e}")
        return False
    
    # 步骤5: 测试复杂查询
    print("\n步骤5: 测试复杂查询...")
    try:
        conn = pymysql.connect(
            host="localhost",
            user="root",
            password="123456",
            database="kylin_2025",
            charset="utf8mb4",
            connect_timeout=10
        )
        
        with conn.cursor() as cursor:
            cursor.execute("""
                SELECT plan_id, problem_type, severity, risk_level, created_at 
                FROM execution_plans 
                ORDER BY created_at DESC 
                LIMIT 5
            """)
            rows = cursor.fetchall()
            print(f"✅ 复杂查询成功，返回 {len(rows)} 行数据")
            
            if rows:
                for i, row in enumerate(rows, 1):
                    print(f"   {i}. {row[0]}: {row[1]} (严重程度: {row[2]}, 风险: {row[3]})")
            else:
                print("   暂无数据")
        
        conn.close()
    except Exception as e:
        print(f"❌ 复杂查询失败: {e}")
        return False
    
    print(f"\n✅ 所有测试通过！")
    return True

def test_insert_and_query():
    """测试插入和查询"""
    print(f"\n🧪 测试插入和查询")
    print("=" * 50)
    
    try:
        conn = pymysql.connect(
            host="localhost",
            user="root",
            password="123456",
            database="kylin_2025",
            charset="utf8mb4",
            connect_timeout=10
        )
        
        with conn.cursor() as cursor:
            # 插入测试数据
            test_plan = {
                "plan_id": f"debug_test_{datetime.now().strftime('%Y%m%d_%H%M%S')}",
                "problem_type": "debug_test",
                "severity": 5.0,
                "description": "调试测试数据",
                "affected_services": '["test_service"]',
                "suggested_actions": '["test_action"]',
                "confidence": 0.8,
                "reasoning": "调试测试",
                "created_at": datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                "tasks_json": '[{"task_id": "debug_task", "description": "调试任务"}]',
                "execution_order_json": '["debug_task"]',
                "estimated_total_time": 100,
                "risk_level": "medium"
            }
            
            sql = '''
            INSERT INTO execution_plans (
                plan_id, problem_type, severity, description, affected_services,
                suggested_actions, confidence, reasoning, created_at,
                tasks_json, execution_order_json, estimated_total_time, risk_level
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            '''
            
            cursor.execute(sql, (
                test_plan["plan_id"],
                test_plan["problem_type"],
                test_plan["severity"],
                test_plan["description"],
                test_plan["affected_services"],
                test_plan["suggested_actions"],
                test_plan["confidence"],
                test_plan["reasoning"],
                test_plan["created_at"],
                test_plan["tasks_json"],
                test_plan["execution_order_json"],
                test_plan["estimated_total_time"],
                test_plan["risk_level"]
            ))
            
            print(f"✅ 测试数据插入成功: {test_plan['plan_id']}")
            
            # 立即查询验证
            cursor.execute("SELECT COUNT(*) FROM execution_plans")
            result = cursor.fetchone()
            count = result[0] if result else 0
            print(f"✅ 插入后记录总数: {count}")
            
            # 查询刚插入的数据
            cursor.execute("SELECT * FROM execution_plans WHERE plan_id = %s", (test_plan["plan_id"],))
            row = cursor.fetchone()
            if row:
                print(f"✅ 查询刚插入的数据成功")
                print(f"   计划ID: {row[0]}")
                print(f"   问题类型: {row[1]}")
                print(f"   严重程度: {row[2]}")
            else:
                print(f"❌ 查询刚插入的数据失败")
        
        conn.close()
        
    except Exception as e:
        print(f"❌ 插入和查询测试失败: {e}")

def main():
    """主函数"""
    print("🔍 MySQL连接调试工具")
    print("=" * 50)
    
    # 逐步测试连接
    if test_mysql_connection_step_by_step():
        # 如果基本连接正常，测试插入和查询
        test_insert_and_query()
    else:
        print("❌ 基本连接测试失败，请检查MySQL服务状态")

if __name__ == "__main__":
    main() 